package com.capgemini.fs.app.ekms.persistence.dao.implementations;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.capgemini.fs.app.ekms.model.Role;
import com.capgemini.fs.app.ekms.model.User;
import com.capgemini.fs.app.ekms.persistence.dao.UserDAO;
import com.capgemini.fs.app.ekms.persistence.util.QueryHelper;

public class UserDAOImpl extends BaseDAOHibernateImpl<User> implements UserDAO {

	public void addUser(User user) {
		save(user);
	}

	@SuppressWarnings("unchecked")
	public User getUserByName(String name) {
		String hql = "from User user where user.userName=?";
		List<User> users = (List<User>) getHibernateTemplate().find(hql, name);
		return users.isEmpty() ? null : (User) users.get(0);
	}

	public void addRoleToUser(User user, Role role) {
		user.getRoles().add(role);
		getHibernateTemplate().update(user);
	}



	public List<User> queryUser(final String firstName, final String lastName,
			final String userName, final Integer status) {
		return queryUser(userName, firstName, lastName, null, null, null, null,
				null, null, status, null);
	}

	public List<User> queryUser(final String firstName, final String lastName,
			final String userName, final Integer status,
			final Integer memberType, final String managerName) {

		return queryUser(userName, firstName, lastName, null, null, null, null,
				null, null, status, managerName);
	}

	@SuppressWarnings("unchecked")
	public List<User> queryUser(final String firstName, final String lastName,
			final String userName, final Integer status, final String email,
			final String phoneNumber, final Integer memberType,
			final List<String> roles, final List<String> groups) {

		List<User> list = (List<User>) getHibernateTemplate().executeFind(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String roleHql = "";
						String groupHql = "";
						QueryHelper qh = new QueryHelper();
						qh.createAlias("User", "user");

						if (!QueryHelper.isNullOrEmpty(firstName)) {
							qh.addRestriction(new QueryHelper.Restriction()
									.like("user.firstName", "%" + firstName
											+ "%"));
						}
						// LastName
						if (!QueryHelper.isNullOrEmpty(lastName)) {
							qh
									.addRestriction(new QueryHelper.Restriction()
											.like("user.lastName", "%"
													+ lastName + "%"));
						}
						// UserName
						if (!QueryHelper.isNullOrEmpty(userName)) {
							qh
									.addRestriction(new QueryHelper.Restriction()
											.like("user.userName", "%"
													+ userName + "%"));
						}
						// Status
						if (!QueryHelper.isNullOrEmpty(status)) {
							qh.addRestriction(new QueryHelper.Restriction().eq(
									"user.status", status));

						}
						// Email
						if (!QueryHelper.isNullOrEmpty(email)) {
							qh.addRestriction(new QueryHelper.Restriction()
									.like("user.email", "%" + email + "%"));
						}
						// PhoneNumber
						if (!QueryHelper.isNullOrEmpty(phoneNumber)) {
							qh.addRestriction(new QueryHelper.Restriction()
									.like("user.phoneNo", "%" + phoneNumber
											+ "%"));
						}
						// MemberType
						if (!QueryHelper.isNullOrEmpty(memberType)) {
							qh.addRestriction(new QueryHelper.Restriction().eq(
									"user.memberType", memberType));
						}
						// Roles
						if (!QueryHelper.isNullOrEmpty(roles)) {
							QueryHelper qhRole = new QueryHelper();
							qhRole.createAlias("User", "usersR");
							qhRole.innerJoin("usersR.roles", "roles");
							qhRole.addRestriction(new QueryHelper.Restriction()
									.in("roles.name", roles));
							qhRole.groupBy("usersR.id");
							qhRole.having("count(*)>=" + roles.size());
							qhRole.selectField("usersR.id", "usersR_id");
							roleHql = qhRole.queryBuilder().toString();
							// Query userRoleQuery =
							// session.createQuery(roleHql);
							// userRoleIds = userRoleQuery.list();

						}
						// Groups
						if (!QueryHelper.isNullOrEmpty(groups)) {
							QueryHelper qhGroup = new QueryHelper();
							qhGroup.createAlias("User", "usersG");
							qhGroup.innerJoin("usersG.groups", "groups");
							qhGroup
									.addRestriction(new QueryHelper.Restriction()
											.in("groups.groupName", groups));
							qhGroup.groupBy("usersG.id");
							qhGroup.having("count(*)>=" + groups.size());
							qhGroup.selectField("usersG.id", "usersG_id");
							groupHql = qhGroup.queryBuilder().toString();
							// Query userGroupQuery =
							// session.createQuery(groupHql);
							// userGroupIds = userGroupQuery.list();
						}
						String subQuery = "";
						if (roleHql.equals("")) {
							if (groupHql.equals("")) {
								subQuery = "";
							} else {
								subQuery = groupHql;
							}
						} else {
							if (groupHql.equals("")) {
								subQuery = roleHql;
							} else {
								QueryHelper roleGroup = new QueryHelper();

								roleGroup.createAlias("User", "users");
								roleGroup
										.addRestriction(new QueryHelper.Restriction()
												.in("users.id", roleHql));
								roleGroup
										.addRestriction(new QueryHelper.Restriction()
												.in("users.id", groupHql));
								roleGroup.selectDistinctField("users.id");
								subQuery = roleGroup.queryBuilder().toString();
							}
						}

						// if (!roleHql.equals("") && !groupHql.equals("")) {
						//	                   
						// } else if (roleHql.equals("") && groupHql.equals(""))
						// {
						// subQuery = "";
						// } else if (roleHql.equals("") &&
						// !groupHql.equals("")) {
						// subQuery = groupHql;
						// } else if (!roleHql.equals("") &&
						// groupHql.equals("")) {
						// subQuery = roleHql;
						// }

						if (!subQuery.equals("")) {
							qh.addRestriction(new QueryHelper.Restriction().in(
									"user.id", subQuery));
						}
						qh.selectField("user");
						String hql = qh.queryBuilder().toString();

						Query query = session.createQuery(hql);
						return query.list();
					}
				});

		return list;
	}

	public List<User> queryUser(final String userName, final String grade,
			final String groupName, final Integer yearOfExperience,
			final Date fromDate, final Date toDate, final Integer memberType) {

		return queryUser(userName, null, null, grade, groupName,
				yearOfExperience, fromDate, toDate, memberType, null, null);
	}

	public List<User> queryUser(final String userName, final String firstName,
			final String lastName, final String grade, final String groupName,
			final Integer yearOfExperience, final Date fromDate,
			final Date toDate, final Integer memberType, final Integer status,
			final String managerName) {
		return queryUser(userName, firstName, lastName, grade, groupName,
				yearOfExperience, fromDate, toDate, memberType, status,
				managerName, null);
	}

	@SuppressWarnings("unchecked")
	public List<User> queryUser(final String userName, final String firstName,
			final String lastName, final String grade, final String groupName,
			final Integer yearOfExperience, final Date fromDate,
			final Date toDate, final Integer memberType, final Integer status,
			final String managerName, final String name) {

		List<User> userList = (List<User>) getHibernateTemplate().executeFind(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						Criteria c = session.createCriteria(User.class, "user");

						// userName
						if (!QueryHelper.isNullOrEmpty(userName)) {
							c.add(Restrictions.like("userName", "%" + userName
									+ "%"));
						}

						// grade
						if (!QueryHelper.isNullOrEmpty(grade)) {
							c.createAlias("user.actualGrade", "grade");
							c.add(Restrictions.like("grade.gradeName", grade
									+ "%"));
						}

						// groupName
						if (!QueryHelper.isNullOrEmpty(groupName)) {
							c.createAlias("user.groups", "group");

							c.add(Restrictions.like("group.groupName", "%"
									+ groupName + "%"));
						}

						// yearOfExperience
						if (!QueryHelper.isNullOrEmpty(yearOfExperience)) {
							if (!QueryHelper.isNullOrEmpty(grade)) {
								c.add(Restrictions.eq("grade.yearOfExperience",
										yearOfExperience));
							} else {
								c.createAlias("user.actualGrade", "grade");
								c.add(Restrictions.eq("grade.yearOfExperience",
										yearOfExperience));

							}
						}

						// fromDate
						if (!QueryHelper.isNullOrEmpty(fromDate)) {
							c.add(Restrictions.ge("entryDate", fromDate));
						}

						// toDate
						if (!QueryHelper.isNullOrEmpty(toDate)) {
							c.add(Restrictions.le("entryDate", toDate));
						}

						// memberType
						if (!QueryHelper.isNullOrEmpty(memberType)) {
							c.add(Restrictions.eq("memberType", memberType));
						}

						// FirstName
						if (!QueryHelper.isNullOrEmpty(firstName)) {
							c.add(Restrictions.like("firstName", "%"
									+ firstName + "%"));
						}
						// LastName
						if (!QueryHelper.isNullOrEmpty(lastName)) {
							c.add(Restrictions.like("lastName", "%" + lastName
									+ "%"));
						}

						// Status
						if (!QueryHelper.isNullOrEmpty(status)) {
							c.add(Restrictions.eq("status", status));
						}

						// ManagerName
						if (!QueryHelper.isNullOrEmpty(managerName)) {
							c.createAlias("user.manager", "manager");
							c.add(Restrictions.like("manager.userName", "%"
									+ managerName + "%"));
						}

						// name
						if (!QueryHelper.isNullOrEmpty(name)) {
							c.createAlias("user.roles", "role");
							c.add(Restrictions.like("role.name", "%" + name
									+ "%"));
						}

						c.setCacheable(true);
						return c.list();
					}
				});
		return userList;
	}

}
